﻿/********************************************************
 * 类名： CustomerDAL.cs
 * 功能： 客户管理数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2013-11-27	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using PSS.Entity.SetManage.BasicData;
using PSS.Utility;

namespace PSS.DAL.SetManage.BasicData
{
    public class CustomerDAL : DbHelperBase
    {
        public bool AddCustomer(CustomerEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_Customer]([CustomerId],[CustomerCode],[CustomerName],[CCategoryId],[BalanceDate],[InitialRec],[InitialDeposit],[Remark]) ");
            strSql.Append(" VALUES(@CustomerId,@CustomerCode,@CustomerName,@CCategoryId,@BalanceDate,@InitialRec,@InitialDeposit,@Remark) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CustomerId", model.CustomerId);
                    Db.AddInParameter(cmd, "@CustomerCode", model.CustomerCode);
                    Db.AddInParameter(cmd, "@CustomerName", model.CustomerName);
                    Db.AddInParameter(cmd, "@CCategoryId", model.CCategoryId);
                    Db.AddInParameter(cmd, "@BalanceDate", model.BalanceDate);
                    Db.AddInParameter(cmd, "@InitialRec", model.InitialRec);
                    Db.AddInParameter(cmd, "@InitialDeposit", model.InitialDeposit);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public void AddCustomerContacts(List<CustomerContactsEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapContactId =
                    new SqlBulkCopyColumnMapping("CContactId", "CContactId");
                bcp.ColumnMappings.Add(mapContactId);

                var mapCustomerId =
                    new SqlBulkCopyColumnMapping("CustomerId", "CustomerId");
                bcp.ColumnMappings.Add(mapCustomerId);

                var mapLinkman =
                    new SqlBulkCopyColumnMapping("Linkman", "Linkman");
                bcp.ColumnMappings.Add(mapLinkman);

                var mapMobilePhone =
                    new SqlBulkCopyColumnMapping("MobilePhone", "MobilePhone");
                bcp.ColumnMappings.Add(mapMobilePhone);

                var mapSpecialPlane =
                   new SqlBulkCopyColumnMapping("SpecialPlane", "SpecialPlane");
                bcp.ColumnMappings.Add(mapSpecialPlane);

                var mapQqMsn =
                   new SqlBulkCopyColumnMapping("QqMsn", "QqMsn");
                bcp.ColumnMappings.Add(mapQqMsn);

                var mapDeliveryAddress =
                   new SqlBulkCopyColumnMapping("DeliveryAddress", "DeliveryAddress");
                bcp.ColumnMappings.Add(mapDeliveryAddress);

                var mapFlag =
                   new SqlBulkCopyColumnMapping("Flag", "Flag");
                bcp.ColumnMappings.Add(mapFlag);

                bcp.DestinationTableName = "PSS_CustomerContacts";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public int GetTotal(CustomerQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT count(*) AS [Total] FROM PSS_Customer AS A  ");
            strSql.Append(" LEFT JOIN PSS_CustomerCategory AS B ON A.CCategoryId=B.CCategoryId ");
            strSql.Append(" LEFT JOIN (SELECT * FROM(SELECT * FROM PSS_CustomerContacts WHERE Flag=1) AS X) AS T ON A.CustomerId=T.CustomerId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.CCategoryId))
            {
                strSql.Append(" AND A.CCategoryId=@CCategoryId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerCode))
            {
                strSql.Append(" AND A.CustomerCode LIKE '%" + qcmodel.CustomerCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerName))
            {
                strSql.Append(" AND A.CustomerName LIKE '%" + qcmodel.CustomerName + "%' ");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.CCategoryId))
                    {
                        Db.AddInParameter(cmd, "@CCategoryId", qcmodel.CCategoryId);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<CustomerEntity> GetCustomerList(CustomerQcEntity qcmodel)
        {
            var list = new List<CustomerEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.CreateTime) AS idx,A.CustomerId,A.CCategoryId,B.CCategoryName,A.CustomerCode,A.CustomerName,T.Linkman,T.MobilePhone,T.SpecialPlane,T.QqMsn,(A.InitialRec-A.InitialDeposit) AS Price FROM PSS_Customer AS A  ");
            strSql.Append(" LEFT JOIN PSS_CustomerCategory AS B ON A.CCategoryId=B.CCategoryId ");
            strSql.Append(" LEFT JOIN (SELECT * FROM(SELECT * FROM PSS_CustomerContacts WHERE Flag=1) AS X) AS T ON A.CustomerId=T.CustomerId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.CCategoryId))
            {
                strSql.Append(" AND A.CCategoryId=@CCategoryId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerCode))
            {
                strSql.Append(" AND A.CustomerCode LIKE '%" + qcmodel.CustomerCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerName))
            {
                strSql.Append(" AND A.CustomerName LIKE '%" + qcmodel.CustomerName + "%' ");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.CCategoryId))
                    {
                        Db.AddInParameter(cmd, "@CCategoryId", qcmodel.CCategoryId);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new CustomerEntity();
                            if (reader["CustomerId"] != DBNull.Value)
                                model.CustomerId = new Guid(reader["CustomerId"].ToString());
                            model.CCategoryName = reader["CCategoryName"] == obj ? null : Convert.ToString(reader["CCategoryName"]);
                            model.CustomerCode = reader["CustomerCode"] == obj ? null : Convert.ToString(reader["CustomerCode"]);
                            model.CustomerName = reader["CustomerName"] == obj ? null : Convert.ToString(reader["CustomerName"]);
                            model.Linkman = reader["Linkman"] == obj ? null : Convert.ToString(reader["Linkman"]);
                            model.MobilePhone = reader["MobilePhone"] == obj ? null : Convert.ToString(reader["MobilePhone"]);
                            model.SpecialPlane = reader["SpecialPlane"] == obj ? null : Convert.ToString(reader["SpecialPlane"]);
                            model.QqMsn = reader["QqMsn"] == obj ? null : Convert.ToString(reader["QqMsn"]);
                            if (reader["Price"] != DBNull.Value)
                                model.Price = Convert.ToDecimal(reader["Price"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public CustomerEntity GetCustomerInfo(string id)
        {
            var model = new CustomerEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT A.[CustomerId],A.[CustomerCode],A.[CustomerName],A.[CCategoryId],B.[CCategoryName],A.[BalanceDate],A.[InitialRec],A.[InitialDeposit],A.[Remark] FROM [PSS_Customer] AS A ");
            strSql.Append(" INNER JOIN PSS_CustomerCategory AS B ON A.CCategoryId=B.CCategoryId ");
            strSql.Append(" WHERE CustomerId=@CustomerId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@CustomerId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["CustomerId"] != DBNull.Value)
                                model.CustomerId = new Guid(reader["CustomerId"].ToString());
                            model.CustomerCode = reader["CustomerCode"] == obj ? null : Convert.ToString(reader["CustomerCode"]);
                            model.CustomerName = reader["CustomerName"] == obj ? null : Convert.ToString(reader["CustomerName"]);
                            if (reader["CCategoryId"] != DBNull.Value)
                                model.CCategoryId = new Guid(reader["CCategoryId"].ToString());
                            model.CCategoryName = reader["CCategoryName"] == obj ? null : Convert.ToString(reader["CCategoryName"]);
                            if (reader["BalanceDate"] != DBNull.Value)
                                model.BalanceDate = Convert.ToDateTime(reader["BalanceDate"].ToString());
                            if (reader["InitialRec"] != DBNull.Value)
                                model.InitialRec = Convert.ToDecimal(reader["InitialRec"].ToString());
                            if (reader["InitialDeposit"] != DBNull.Value)
                                model.InitialDeposit = Convert.ToDecimal(reader["InitialDeposit"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public IList<CustomerContactsEntity> GetCustomerContactsList(CustomerContactsQcEntity qcmodel)
        {
            var list = new List<CustomerContactsEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT * FROM PSS_CustomerContacts WHERE CustomerId=@CustomerId  ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CustomerId", qcmodel.CustomerId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new CustomerContactsEntity();
                            if (reader["CContactId"] != DBNull.Value)
                                model.CContactId = new Guid(reader["CContactId"].ToString());
                            model.Linkman = reader["Linkman"] == obj ? null : Convert.ToString(reader["Linkman"]);
                            model.MobilePhone = reader["MobilePhone"] == obj ? null : Convert.ToString(reader["MobilePhone"]);
                            model.SpecialPlane = reader["SpecialPlane"] == obj ? null : Convert.ToString(reader["SpecialPlane"]);
                            model.QqMsn = reader["QqMsn"] == obj ? null : Convert.ToString(reader["QqMsn"]);
                            model.DeliveryAddress = reader["DeliveryAddress"] == obj ? null : Convert.ToString(reader["DeliveryAddress"]);
                            model.SpecialPlane = reader["SpecialPlane"] == obj ? null : Convert.ToString(reader["SpecialPlane"]);
                            model.QqMsn = reader["QqMsn"] == obj ? null : Convert.ToString(reader["QqMsn"]);
                            if (reader["Flag"] != DBNull.Value)
                                model.Flag =(bool)reader["Flag"];
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool ModifyCustomer(CustomerEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_Customer] SET [CustomerCode] = @CustomerCode,[CustomerName] = @CustomerName,[CCategoryId] = @CCategoryId,[BalanceDate] = @BalanceDate,[InitialRec] = @InitialRec,[InitialDeposit] = @InitialDeposit,[Remark] = @Remark WHERE [CustomerId] = @CustomerId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CustomerCode", model.CustomerCode);
                    Db.AddInParameter(cmd, "@CustomerName", model.CustomerName);
                    Db.AddInParameter(cmd, "@CCategoryId", model.CCategoryId);
                    Db.AddInParameter(cmd, "@BalanceDate", model.BalanceDate);
                    Db.AddInParameter(cmd, "@InitialRec", model.InitialRec);
                    Db.AddInParameter(cmd, "@InitialDeposit", model.InitialDeposit);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@CustomerId", model.CustomerId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool DeleteCustomerContacts(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_CustomerContacts] WHERE CustomerId=@CustomerId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CustomerId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveCustomer(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_Customer] WHERE CustomerId=@CustomerId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CustomerId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public IList<CustomerEntity> GetCustomerListByCombobox()
        {
            var list = new List<CustomerEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT CustomerId,CustomerCode,CustomerName FROM PSS_Customer Order by CreateTime  ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new CustomerEntity();
                            if (reader["CustomerId"] != DBNull.Value)
                                model.CustomerId = new Guid(reader["CustomerId"].ToString());
                            model.CustomerCode = reader["CustomerCode"] == obj ? null : Convert.ToString(reader["CustomerCode"]);
                            model.CustomerName = reader["CustomerName"] == obj ? null : Convert.ToString(reader["CustomerName"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
